﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class TaiKhoanDAO
    {
        public int KiemTraUser(TaiKhoanDTO tkdto)
        {
            string sql = "select * from TaiKhoan where TenUser='" + tkdto.TenUser + "' and MatKhau='" + tkdto.MatKhau + "'";
            SqlDataAdapter sqlda = new SqlDataAdapter(sql,DataProvider.sqlconn);
            DataTable dt = new DataTable();
            sqlda.Fill(dt);
            if (dt.Rows.Count == 1)
                return 1;
            else
                return 0;
        }

        public int DoiMatKhau(TaiKhoanDTO tkdto,string mkmoi)
        {
            string sql = "Update TaiKhoan set MatKhau='" + mkmoi + "' where TenUser='" + tkdto.TenUser + "' and MatKhau='" + tkdto.MatKhau + "'";
            DataProvider.sqlconn.Open();
            SqlCommand cmd = new SqlCommand(sql, DataProvider.sqlconn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            if (kq == 1)
                return 1;
            else
                return 0;
        }

        public TaiKhoanDTO LayThongTinTaiKhoan(string tenuser)
        {
            TaiKhoanDTO tkdto = new TaiKhoanDTO();
            string sql = "select * from TaiKhoan where TenUser='" + tenuser + "'";
            SqlDataAdapter sqlda = new SqlDataAdapter(sql, DataProvider.sqlconn);
            DataTable dt = new DataTable();
            sqlda.Fill(dt);
            if (dt.Rows.Count == 1)
            {
                foreach (DataRow dtrow in dt.Rows)
                {
                    tkdto.TenUser = dtrow["TenUser"].ToString();
                    tkdto.MatKhau = dtrow["MatKhau"].ToString();
                    tkdto.TenQuyen = dtrow["TenQuyen"].ToString();
                }
                return tkdto;
            }
            else
                return null;
        }

        public List<TaiKhoanDTO> LayDanhSachTaiKhoan()
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<TaiKhoanDTO> KQ = new List<TaiKhoanDTO>();
            string sql = "select * from TaiKhoan";
            SqlDataAdapter dtadp = new SqlDataAdapter(sql, conn);
            try
            {
                DataTable dt = new DataTable();
                dtadp.Fill(dt);
                TaiKhoanDTO cn;
                foreach (DataRow dtrow in dt.Rows)
                {
                    cn = new TaiKhoanDTO();
                    cn.TenUser = dtrow["TenUser"].ToString();
                    cn.MatKhau = dtrow["MatKhau"].ToString();
                    cn.TenQuyen = dtrow["TenQuyen"].ToString();
                    KQ.Add(cn);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return KQ;
        }

        public int ThemTaiKhoan(TaiKhoanDTO tkdto)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "INSERT INTO TaiKhoan(TenUser,MatKhau,TenQuyen) VALUES('" + tkdto.TenUser + "','" + tkdto.MatKhau + "',N'" + tkdto.TenQuyen + "')";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int CapNhatTaiKhoan(TaiKhoanDTO tkdto)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "Update TaiKhoan set MatKhau='" + tkdto.MatKhau + "',TenQuyen=N'" + tkdto.TenQuyen + "' where TenUser='" + tkdto.TenUser + "'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int XoaTaiKhoan(string TenUser)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "Delete TaiKhoan where TenUser='" + TenUser + "'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public bool TaiKhoanTrung(string Tentk)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "select * from TaiKhoan where TenUser='" + Tentk + "'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            try
            {
                cmd.ExecuteScalar().ToString();
            }
            catch
            {
                conn.Close();
                return false;
            }
            conn.Close();
            return true;
        }

        public bool LayTaiKhoan(string TenUser)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "select * from HoaDon where TenUser='" + TenUser + "'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            try
            {
                cmd.ExecuteScalar().ToString();
            }
            catch
            {
                conn.Close();
                return false;
            }
            conn.Close();
            return true;
        }
    }
}
